<?php

class mysqlqueries  {
        
    public $Addition='addition';
    public $Additiontype='additiontype';
    public $Configuration='configuration';
    public $Customqueries='customqueries';
    public $Device='device';
    public $Devicetype='devicetype';
    public $Flag='flag';
    public $Level='level';
    public $Mac='mac';
    public $Patchpanel='patchpanel';
    public $Relocation='relocation';
    public $Room='room';
    public $Supplier='supplier';
    public $Switch='switch';
    public $Switchport='switchport';
    public $Switch_has_vlan='switch_has_vlan';
    public $User='user';
    public $Vlan='vlan';
    

    public function __construct() 
    { 
    }
    public function GetTable($table, $orderTables)
    {
        $order ="";
        if(is_array($orderTables))
        {
            $order = " ORDER BY ";
            for($i=0; $i < count($orderTables); $i++)
            {
                if($i > 0)
                {
                  $order = $order .", " .$orderTables[$i];
                }
                else 
                {
                    $order = $order . $orderTables[$i]; 
                }
            }            
        }else
        {
            if($orderTables != null)
            {
                $order = " ORDER BY ";
                $order = $order . $orderTables;
            } 
        }
 
        $query = 'Select * FROM ' . $table . $order;
        return $query;
    }
    
    public function GetIndex($table ,$index, $column, $orderTables)
    {
        $order ="";
        if(is_array($orderTables))
        {
            $order = " ORDER BY ";
            for($i=0; $i < count($orderTables); $i++)
            {
                if($i > 0)
                {
                  $order = $order .", " .$orderTables[$i];
                }
                else 
                {
                    $order = $order . $orderTables[$i]; 
                }
            }            
        }else
        {
            if($orderTables != null)
            {
                $order = " ORDER BY ";
                $order = $order . $orderTables;
            } 
        }
        
        if(is_array($column))
        {
            for($i=0; $i < count($column); $i++)
            {
                if($i > 0)
                {
                  $where = $where .'and '. $column[$i] .' = "' .$index[$i].'"';
                }
                else 
                {
                    $where = $column[$i] .' = "' . $index[$i] .'"'; 
                }
            }            
        }else
        {
            if($column == null)
            {
                $column = 'ID';
            }
          
            $where = $column ." = '" . $index ."'";     
        }
 
     
        $query = 'Select * FROM ' . $table . ' WHERE '. $where . $order;
        return $query;
    }    
    
    public function GetRange($table, $startIndex, $endindex, $column, $orderTables)
    {
                $order ="";
        if(is_array($orderTables))
        {
            $order = "ORDER BY ";
            for($i=0; $i < count($orderTables); $i++)
            {
                if($i > 0)
                {
                  $order = $order .", " .$orderTables[$i];
                }
                else 
                {
                    $order = $order . $orderTables[$i]; 
                }
            }            
        }else
        {
            if($orderTables != null)
            {
                $order = "ORDER BY ";
                $order = $order . $orderTables;
            } 
        }
        
        if($column == null)
        {
            $column = 'ID';
        }
        $query = 'Select * FROM ' . $table . ' WHERE ' . $column . ' BETWEEN ' . $startIndex . ' AND ' . $endindex . $order;
        return $query;  
    }
    
    public function Update($table, $argument, $id, $column, $selectColumn) //arguments 
    { 
        if($column == null || $argument == null || $table == null || $id == null)   //if all arguments are set return
        {
            return "";
        }
        if($selectColumn == null)   //If $selectColumn = null select the ID column
        {
          $selectColumn = "ID";
        }
        $query = "UPDATE ".$table." SET ";
        if(!is_array($column))      //if $colum is no array
        {
          if(is_array($argument))   //if $argument is a array return
          {
            return "";
          }
          //If $column and $argument is no array add these to the querie
          $query.="$column='".sanitizeInputString($argument)."'";
        }
        else
        {
          if(!is_array($argument) || count($column) != count($argument))
          {
            return "";
          }
          //If $column and $argument are arrays and both have the same length
          //foreach element add the infromations to the querie
          $fields = "";
          for($i=0;$i<count($column);$i++)
          {
            $fields.=$column[$i]."='".sanitizeInputString($argument[$i])."'";
            if($i!=(count($column)-1))
            {
              $fields.=", ";
            }
          }
          $query.=$fields;

        }
        $query.=" WHERE $selectColumn='$id'";
        return $query; //return the querie
    }
    
    public function Add ($table, $argument)
    {        
        $insert = '';
        if(!is_array($argument[0]))
        {
          for($i = 0; $i < count($argument); $i++)
          {
            if($argument[0] == null && $i == 0)
            {    $i++;
            }
            
                $insert = $insert . " , '" . sanitizeInputString($argument[$i]) . "'";
          }       
          $query = "INSERT INTO " . $table . " Values (NULL" . $insert . ")";
        }
        else
        {
          $first = true;
          $fullInsert = "";
          foreach($argument as $arg)
          {
            $insert = "";
            for($i = 0; $i < count($arg); $i++)
            {
              if($arg[0] == null && $i == 0)
                $i++;
            
                $insert = $insert . " , '" . sanitizeInputString($arg[$i]) . "'";
                
            }  
            
            if($first)
            {
              $first=false;
            }
            else 
            {
              $fullInsert = $fullInsert . ",";
            }
            $fullInsert = $fullInsert . "(NULL" . $insert . ")";
          }
          $query = "INSERT INTO " . $table . " Values " . $fullInsert;
        }
        return $query;
    }
    
    public function Remove($table,$column,$argument) {
      if($column == null)
        $column = "ID";
      
      $condition = "";
      if(is_array($column))
      {
        if(!is_array($argument) || count($column)!=count($argument))
          return false;
        if(count($column)>0)
        {
          $condition="$column[0]='$argument[0]'";
          for($i=1;$i<count($column);$i++)
          {
            $condition.=" AND $column[$i]='$argument[$i]'";
          }
        }
        else 
        {
          return false;
        }
        
      }
      else
      {
        if(is_array($argument))
          return false;
        
        $condition = "$column='$argument'";
      }
      $query="DELETE FROM $table WHERE $condition";
      return $query;
    }
    
    public function AddLimit($query, $lowerLimit, $upperLimit) {
      return $query . " LIMIT $lowerLimit, $upperLimit";
    }
    
    public function ExtendWhere($query, $column, $operand, $argument) {
      $where = " WHERE ";
      if(is_array($column) && is_array($operand) && is_array($argument))
      {
        if(count($column) == count($operand) && count($argument) == count($column) && count($column) > 0)
        {
          $where .=$column[0] . " " . $operand[0] . " '" . $argument[0] . "'";
          for($i = 1; $i < count($column); $i++)
          {
            $where.=" AND " . $column[$i] . " " . $operand[$i] . " '" . $argument[$i] . "'";
          }
        }
        else
          return false;
      }
      else if(!is_array($column) && !is_array($operand) && !is_array($argument))
      {
            $where.=" " . $column . " " . $operand . " '" . $argument . "'";
      }
      else
        return false;
      
      return $query . $where;
    }
    
    function ExtendOrder($query, $orderTables)
    {
      $order ="";
        if(is_array($orderTables))
        {
            $order = " ORDER BY ";
            for($i=0; $i < count($orderTables); $i++)
            {
                if($i > 0)
                {
                  $order = $order .", " .$orderTables[$i];
                }
                else 
                {
                    $order = $order . $orderTables[$i]; 
                }
            }            
        }else
        {
          $order = " ORDER BY ";
          $order = $order . $orderTables;
            
        }
        return $query . $order;
    }
    
    public function TruncateTable($table) {
      return "TRUNCATE " . $table;
    }
}
